﻿using System;
using System.Collections;
using System.Data;
using System.IO;
using System.Text;

namespace DataUpload.ReadFiles
{
    public class TextFile: ParsedFile
    {
        private string _columnSeperator = ",";
        private string _recordSeperator = Environment.NewLine;
        private string _quoteMark = "\"";
        private bool _hasTablesName = false;
        private ArrayList _sColumns;
        private ArrayList _tColumns;
        private ArrayList _tColumns_index;

        private long _fileLength;

        #region Seperators properties
        public string FieldSeperator
        {
            get { return _columnSeperator; }
            set
            {
                value = value.Replace("\\r", "\r");
                value = value.Replace("\\n", "\n");
                value = value.Replace("\\t", "\t");
                _columnSeperator = value;
            }
        }

        public string RecordSeperator
        {
            get { return _recordSeperator; }
            set
            {
                value = value.Replace("\\r", "\r");
                value = value.Replace("\\n", "\n");
                value = value.Replace("\\t", "\t");
                _recordSeperator = value;
            }
        }

        public string QuoteMark
        {
            get { return _quoteMark; }
            set { _quoteMark = value; }
        }

        public bool HasTablesName
        {
            get { return _hasTablesName; }
            set { _hasTablesName = value; }
        }
        #endregion;

        #region Constructor
        public TextFile(string filePath)
            : base(filePath)
        {
            if (File.Exists(filePath) == false)
                throw new FileNotFoundException("Please verify this path: " + filePath);

            FileInfo thisFile = new FileInfo(filePath);
            _fileLength = thisFile.Length;
        }

        ~TextFile()
        {
            Clear();
        }
        #endregion;

        #region Other functions
        public override string ToString()
        {
            return "TextFile";
        }
        #endregion;

        #region Implement abstract functions
        public override ArrayList GetColumnsList()
        {
            if (File.Exists(FilePath) == false)
                throw new FileNotFoundException("Please verify this path: " + FilePath);

            string action = "Attempting to open " + FilePath;
            FileInfo thisFile = new FileInfo(FilePath);
            StreamReader fileReader;
            if (thisFile.Length < 32 * 1024)
                fileReader = new StreamReader(FilePath, Encoding.UTF8, true);
            else
                fileReader = new StreamReader(FilePath, Encoding.UTF8, true, 32 * 1024 / sizeof(char));

            try
            {
                action = "Attempting to get list of available columns";
                _columns.Clear();

                string content = "";
                string tableName = "";
                ArrayList cols = new ArrayList();

                if (thisFile.Length <= 32 * 1024)
                {
                    content = fileReader.ReadToEnd();
                    ReadColumns(ref content, ref tableName, ref cols, ref action, false);
                }
                else
                {
                    bool isCont = false;
                    do
                    {
                        char[] charArr = new char[32 * 1024 / sizeof(char)];
                        fileReader.ReadBlock(charArr, 0, 32 * 1024 / sizeof(char));
                        content = new string(charArr);
                        isCont = ReadColumns(ref content, ref tableName, ref cols, ref action, isCont);
                    } while (isCont == true);
                }

                AddColumns(tableName, cols);
                return _columns;
            }
            catch (Exception exc)
            {
                DBConnection.MyDebugClass.LogThis(action, exc);
                throw exc;
            }
            finally
            {
                fileReader.Close();
                fileReader.Dispose();
            }
        }

        public override void StartUpload(ref DBConnection.DBConnection connection)
        {
            if (_mapStatus != MapStatus.MappingDone)
                return;

            if (!File.Exists(_filePath))
                throw new FileNotFoundException("Please verify this path: " + _filePath);

            _rowsInserted = 0;
            _rowsFailed.Clear();
            _startUpload = DateTime.Now;
            _passRows = 0;
            if (_totalRows == 0)
                TotalRows();

            string data = "";
            ArrayList temp = new ArrayList();
            RowsInsertedEventArgs e;
            if (_fileLength <= 32768)
            {
                using (StreamReader fileReader = new StreamReader(_filePath, Encoding.UTF8, true))
                {
                    data = fileReader.ReadToEnd();
                    if (_hasTablesName)
                        data = data.Substring(data.IndexOf(_recordSeperator) + _recordSeperator.Length);
                    data = data.Substring(data.IndexOf(_recordSeperator) + _recordSeperator.Length);

                    do
                    {
                        if (_stopThis)
                        {
                            fileReader.Close();
                            _endUpload = DateTime.Now;
                            return;
                        }
                        if (data.Contains(_recordSeperator))
                        {
                            if (data.StartsWith(_quoteMark))
                            {
                                temp.Add(data.Substring(1, data.IndexOf(_quoteMark + _recordSeperator) - 1));
                                data = data.Substring(data.IndexOf(_quoteMark + _recordSeperator) + (_quoteMark + _recordSeperator).Length);
                            }
                            else
                            {
                                temp.Add(data.Substring(0, data.IndexOf(_recordSeperator)));
                                data = data.Substring(data.IndexOf(_recordSeperator) + _recordSeperator.Length);
                            }
                            //++_passRows;
                        }
                        else
                        {
                            temp.Add(data);
                            data = "";
                            //++_passRows;
                        }
                    } while (data != "");

                    e = new RowsInsertedEventArgs(0, 0, "Generating scripts");
                    OnRowsInsertedEvent(this, e);
                    _passRows = temp.Count;
                    ArrayList scripts = GenerateScripts(ref temp);
                    e = new RowsInsertedEventArgs(0, 0, "Uploading " + _filePath);
                    OnRowsInsertedEvent(this, e);
                    UploadThis(scripts, ref connection);

                    if (_stopThis)
                    {
                        fileReader.Close();
                        _endUpload = DateTime.Now;
                        return;
                    }
                }
            }
            else
            {
                char[] tempArr = new char[16384];
                using (StreamReader fileReader = new StreamReader(_filePath, Encoding.UTF8, true, 16384))
                {
                    fileReader.ReadBlock(tempArr, 0, 16384);
                    data = new string(tempArr);
                    if (_hasTablesName)
                        data = data.Substring(data.IndexOf(_recordSeperator) + _recordSeperator.Length);
                    while (!data.Contains(_recordSeperator) && !fileReader.EndOfStream) // huge number of columns
                    {
                        FillEmptyStr(ref tempArr);
                        fileReader.ReadBlock(tempArr, 0, 16384);
                        data += new string(tempArr);
                        data = data.TrimEnd();
                    }
                    data = data.Substring(data.IndexOf(_recordSeperator) + _recordSeperator.Length);

                    ArrayList scripts;
                    do
                    {
                        if (_stopThis)
                        {
                            fileReader.Close();
                            _endUpload = DateTime.Now;
                            return;
                        }
                        if (data.Contains(_recordSeperator) == false)
                        {
                            if (fileReader.EndOfStream == false)
                            {
                                e = new RowsInsertedEventArgs(_rowsInserted, (uint)_rowsFailed.Count, "Generating scripts");
                                OnRowsInsertedEvent(this, e);
                                _passRows += temp.Count;
                                scripts = GenerateScripts(ref temp);
                                e = new RowsInsertedEventArgs(_rowsInserted, (uint)_rowsFailed.Count, "Uploading " + _filePath);
                                OnRowsInsertedEvent(this, e);
                                UploadThis(scripts, ref connection);

                                if (_stopThis)
                                {
                                    fileReader.Close();
                                    _endUpload = DateTime.Now;
                                    return;
                                }

                                FillEmptyStr(ref tempArr);
                                fileReader.ReadBlock(tempArr, 0, 16384);
                                data += new string(tempArr);
                                data = data.Trim();
                                temp.Clear();
                                //temp = new ArrayList(); --> which one is better: clear or new?
                            }
                            else
                            {
                                if (data.Trim() != "")
                                    temp.Add(data.Trim());
                                //++_passRows;
                            }
                        }
                        else
                        {
                            data = data.Trim();
                            if (data.StartsWith(_quoteMark))
                            {
                                temp.Add(data.Substring(1, data.IndexOf(_quoteMark + _recordSeperator) - 1));
                                data = data.Substring(data.IndexOf(_quoteMark + _recordSeperator) + (_quoteMark + _recordSeperator).Length);
                            }
                            else
                            {
                                temp.Add(data.Substring(0, data.IndexOf(_recordSeperator)));
                                data = data.Substring(data.IndexOf(_recordSeperator) + _recordSeperator.Length);
                            }
                            //++_passRows;
                        }
                    } while (!fileReader.EndOfStream);

                    e = new RowsInsertedEventArgs(_rowsInserted, (uint)_rowsFailed.Count, "Generating scripts");
                    OnRowsInsertedEvent(this, e);
                    _passRows += temp.Count;
                    scripts = GenerateScripts(ref temp);
                    e = new RowsInsertedEventArgs(_rowsInserted, (uint)_rowsFailed.Count, "Uploading " + _filePath);
                    OnRowsInsertedEvent(this, e);
                    UploadThis(scripts, ref connection);
                }
            }
            _passRows = _totalRows;
            _endUpload = DateTime.Now;
            e = new RowsInsertedEventArgs(_rowsInserted, (uint)_rowsFailed.Count, "Uploading " + _filePath + ": Done");
            OnRowsInsertedEvent(this, e);
        }

        public override bool Clear()
        {
            if (_columns != null)
                _columns.Clear();
            if (_rowsFailed != null)
                _rowsFailed.Clear();
            if (_sColumns != null)
                _sColumns.Clear();
            if (_sourceColumns != null)
                _sourceColumns.Clear();
            if (_targetColumns != null)
                _targetColumns.Clear();
            if (_targetType != null)
                _targetType.Clear();
            if (_tColumns != null)
                _tColumns.Clear();
            return true;
        }

        public override void Dispose()
        {
            Clear();
        }

        public override SortedList TakeSample(ArrayList columns, int numSample)
        {
            if (numSample == 0)
                return new SortedList();
            if (!File.Exists(_filePath))
                throw new FileNotFoundException("Please verify this path: " + _filePath);

            string sample = "";
            ArrayList temp = new ArrayList();
            if (_fileLength <= 32768)
            {
                using (StreamReader fileReader = new StreamReader(_filePath, Encoding.UTF8, true))
                {
                    sample = fileReader.ReadToEnd();
                    if (_hasTablesName)
                        sample = sample.Substring(sample.IndexOf(_recordSeperator) + _recordSeperator.Length);
                    sample = sample.Substring(sample.IndexOf(_recordSeperator) + _recordSeperator.Length);

                    int count = 0;
                    do
                    {
                        if (sample.Contains(_recordSeperator))
                        {
                            if (sample.StartsWith(_quoteMark))
                            {
                                temp.Add(sample.Substring(1, sample.IndexOf(_quoteMark + _recordSeperator) - 1));
                                sample = sample.Substring(sample.IndexOf(_quoteMark + _recordSeperator) + (_quoteMark + _recordSeperator).Length);
                            }
                            else
                            {
                                temp.Add(sample.Substring(0, sample.IndexOf(_recordSeperator)));
                                sample = sample.Substring(sample.IndexOf(_recordSeperator) + _recordSeperator.Length);
                            }
                        }
                        else
                        {
                            temp.Add(sample);
                            sample = "";
                        }
                        ++count;
                    } while (count < numSample && sample != "");
                }
            }
            else
            {
                char[] tempArr = new char[16384];
                using (StreamReader fileReader = new StreamReader(_filePath, Encoding.UTF8, true, 16384))
                {
                    fileReader.ReadBlock(tempArr, 0, 16384);
                    sample = new string(tempArr);
                    if (_hasTablesName)
                        sample = sample.Substring(sample.IndexOf(_recordSeperator) + _recordSeperator.Length);
                    while (!sample.Contains(_recordSeperator) && !fileReader.EndOfStream) // huge number of columns
                    {
                        FillEmptyStr(ref tempArr);
                        fileReader.ReadBlock(tempArr, 0, 16384);
                        sample += new string(tempArr);
                        sample = sample.TrimEnd();
                    }
                    sample = sample.Substring(sample.IndexOf(_recordSeperator) + _recordSeperator.Length);

                    int count = 0;
                    do
                    {
                        if (sample.Contains(_recordSeperator) == false)
                        {
                            if (count < numSample && fileReader.EndOfStream == false)
                            {
                                FillEmptyStr(ref tempArr);
                                fileReader.ReadBlock(tempArr, 0, 16384);
                                sample += new string(tempArr);
                                sample = sample.Trim();
                            }
                        }
                        else
                        {
                            sample = sample.Trim();
                            if (sample.StartsWith(_quoteMark))
                            {
                                temp.Add(sample.Substring(1, sample.IndexOf(_quoteMark + _recordSeperator) - 1));
                                sample = sample.Substring(sample.IndexOf(_quoteMark + _recordSeperator) + (_quoteMark + _recordSeperator).Length);
                            }
                            else
                            {
                                temp.Add(sample.Substring(0, sample.IndexOf(_recordSeperator)));
                                sample = sample.Substring(sample.IndexOf(_recordSeperator) + _recordSeperator.Length);
                            }
                        }
                        ++count;
                    } while (count < numSample && !fileReader.EndOfStream);
                }
            }

            ArrayList columnIndex = new ArrayList();
            SortedList result = new SortedList();
            foreach (string s in columns)
            {
                int index = _columns.IndexOf(s);
                if (!columnIndex.Contains(index))
                {
                    columnIndex.Add(index);
                    ArrayList col = new ArrayList();
                    result.Add(index, col);
                }
            }
            //columnIndex.Sort(); --> we suppose the columns has been sorted

            for (int i = 0; i < temp.Count; i++)
            {
                string row = temp[i].ToString();
                string aColumn = "";
                int j = 0;
                do
                {
                    if (row.Contains(_columnSeperator))
                    {
                        if (row.StartsWith(_quoteMark))
                        {
                            aColumn = row.Substring(1, row.IndexOf(_quoteMark + _columnSeperator) - 1);
                            row = row.Substring(row.IndexOf(_quoteMark + _columnSeperator) + (_quoteMark + _columnSeperator).Length);
                        }
                        else
                        {
                            aColumn = row.Substring(0, row.IndexOf(_columnSeperator));
                            row = row.Substring(row.IndexOf(_columnSeperator) + _columnSeperator.Length);
                        }
                    }
                    else
                    {
                        aColumn = row;
                        row = "";
                    }
                    if (result.ContainsKey(j))
                        ((ArrayList)result[j]).Add(aColumn);
                    j++;
                } while (row != "");
            }

            return result;
        }

        public override long TotalRows()
        {
            string data;
            int startPos = 0;
            if (_hasTablesName)
                _totalRows = -1;
            else
                _totalRows = 0;

            if (_fileLength <= 32768)
            {
                using (StreamReader fileReader = new StreamReader(_filePath, Encoding.UTF8, true))
                {
                    data = fileReader.ReadToEnd();
                    while ((startPos = data.IndexOf(_recordSeperator, startPos) + _recordSeperator.Length) >= _recordSeperator.Length)
                    {
                        ++_totalRows;
                    }
                }
            }
            else
            {
                char[] buffer = new char[16384];
                data = "";
                using (StreamReader fileReader = new StreamReader(_filePath, Encoding.UTF8, true, 16384))
                {
                    do
                    {
                        FillEmptyStr(ref buffer);
                        fileReader.ReadBlock(buffer, 0, 16384);
                        data += (new string(buffer)).Trim();
                        while ((startPos = data.IndexOf(_recordSeperator, startPos) + _recordSeperator.Length) >= _recordSeperator.Length)
                        {
                            ++_totalRows;
                        }
                        data = data.Substring(data.LastIndexOf(_recordSeperator) + _recordSeperator.Length);
                    }
                    while (!fileReader.EndOfStream);
                }
            }
            return _totalRows;
        }
        #endregion;

        #region Override virtual functions
        protected override void OrganizeMap(ref ArrayList sourceColumns, ref ArrayList targetColumns, ref ArrayList targetType)
        {
            base.OrganizeMap(ref sourceColumns, ref targetColumns, ref targetType);

            _sColumns = new ArrayList();
            _tColumns = new ArrayList();
            _tColumns_index = new ArrayList();

            string tbName = targetColumns[0].ToString().Substring(0, targetColumns[0].ToString().IndexOf("."));
            string sCols = "", tCols_index = "";
            string tCols = "insert into [" + tbName + "](";
            for (int i = 0; i < targetColumns.Count; i++)
            {
                if (targetColumns[i].ToString().Substring(0, targetColumns[i].ToString().IndexOf(".")) == tbName)
                {
                    sCols += _columns.IndexOf(sourceColumns[i]) + ";";
                    tCols += "[" + targetColumns[i].ToString().Substring(targetColumns[i].ToString().IndexOf(".") + 1) + "],";
                    tCols_index += i + ";";
                }
                else
                {
                    sCols = sCols.Remove(sCols.Length - 1);
                    _sColumns.Add(sCols);
                    tCols = tCols.Remove(tCols.Length - 1) + ")";
                    _tColumns.Add(tCols);
                    _tColumns_index.Add(tCols_index);
                    tbName = targetColumns[i].ToString().Substring(0, targetColumns[i].ToString().IndexOf("."));
                    sCols = "";
                    tCols = "insert into [" + tbName + "](";
                    tCols_index = "";
                    i--;
                }
            }

            sCols = sCols.Remove(sCols.Length - 1);
            _sColumns.Add(sCols);
            tCols = tCols.Remove(tCols.Length - 1) + ")";
            _tColumns.Add(tCols);
            _tColumns_index.Add(tCols_index);
        }
        #endregion;

        #region Private functions
        private bool ReadColumns(ref string content, ref string tableName, ref ArrayList cols, ref string action, bool isCont)
        {
            string columnsLine = "";
            tableName = "";
            if (content.StartsWith(_recordSeperator))
                content = content.Remove(0, _recordSeperator.Length);

            if (isCont == true)
            {
                columnsLine = cols[cols.Count - 1].ToString() + content;
                cols.RemoveAt(cols.Count - 1);
            }

            if (_hasTablesName == false)
            {
                action = "Reading the first line: columnsLine";
                if (content.IndexOf(_recordSeperator) > 0)
                {
                    columnsLine = content.Substring(0, content.IndexOf(_recordSeperator)).Trim();
                    content = content.Substring(content.IndexOf(_recordSeperator) + _recordSeperator.Length);
                    isCont = false;
                }
                else
                    isCont = true;
            }
            else
            {
                action = "Reading the first line: tableName";
                if (content.IndexOf(_recordSeperator) == -1)
                    throw new ArgumentException("Table name is too long!");

                tableName = content.Substring(0, content.IndexOf(_recordSeperator)).Trim() + ".";
                content = content.Substring(content.IndexOf(_recordSeperator) + _recordSeperator.Length);

                action = "Reading the second line: columnsLine";
                if (content.IndexOf(_recordSeperator) > 0)
                {
                    columnsLine = content.Substring(0, content.IndexOf(_recordSeperator)).Trim();
                    content = content.Substring(content.IndexOf(_recordSeperator) + _recordSeperator.Length);
                    //TODO: huge number of columns
                    isCont = false;
                }
                else
                    isCont = true;
            }

            action = "Retrieving columns";
            string aColumn = "";
            do
            {
                if (columnsLine.Contains(_columnSeperator))
                {
                    if (columnsLine.StartsWith(_quoteMark))
                    {
                        aColumn = columnsLine.Substring(1, columnsLine.IndexOf(_quoteMark + _columnSeperator) - 1);
                        columnsLine = columnsLine.Substring(columnsLine.IndexOf(_quoteMark + _columnSeperator) + (_quoteMark + _columnSeperator).Length);
                    }
                    else
                    {
                        aColumn = columnsLine.Substring(0, columnsLine.IndexOf(_columnSeperator));
                        columnsLine = columnsLine.Substring(columnsLine.IndexOf(_columnSeperator) + _columnSeperator.Length);
                    }
                    cols.Add(aColumn);
                }
                else
                {
                    action = "Retrieving the last column";
                    cols.Add(columnsLine);
                    columnsLine = "";
                }
            } while (columnsLine != "");
            return isCont;
        }

        private void AddColumns(string tableName, ArrayList cols)
        {
            foreach (string col in cols)
            {
                if (!_columns.Contains(tableName + col))
                    _columns.Add(tableName + col);
            }
        }

        private void FillEmptyStr(ref char[] targetArr)
        {
            for (int i = 0; i < targetArr.Length; i++)
                targetArr[i] = ' ';
        }

        private ArrayList GenerateScripts(ref ArrayList data)
        {
            // Result scripts
            ArrayList scripts = new ArrayList();

            // Insert queries
            string[] cmds = new string[_tColumns.Count];
            // Indexes
            ArrayList columnsIndex = new ArrayList();
            ArrayList types = new ArrayList();
            // Data of queries
            ArrayList queryData = new ArrayList();
            string[] seperator = { ";" };
            for (int i = 0; i < _tColumns.Count; i++)
            {
                cmds[i] = _tColumns[i].ToString() + " values (";
                string[] tmpIndex = _sColumns[i].ToString().Split(seperator, StringSplitOptions.RemoveEmptyEntries);
                columnsIndex.Add(tmpIndex);
                tmpIndex = _tColumns_index[i].ToString().Split(seperator, StringSplitOptions.RemoveEmptyEntries);
                types.Add(tmpIndex);
                string[] tmpData = new string[tmpIndex.Length];
                queryData.Add(tmpData);
            }

            int index;
            string tmpCmd;
            string aColumn;
            for (int i = 0; i < data.Count; i++)
            {
                index = 0;
                do
                {
                    if (data[i].ToString().Contains(_columnSeperator))
                    {
                        if (data[i].ToString().StartsWith(_quoteMark))
                        {
                            aColumn = data[i].ToString().Substring(1, data[i].ToString().IndexOf(_quoteMark + _columnSeperator) - 1);
                            data[i] = data[i].ToString().Substring(data[i].ToString().IndexOf(_quoteMark + _columnSeperator) + (_quoteMark + _columnSeperator).Length);
                        }
                        else
                        {
                            aColumn = data[i].ToString().Substring(0, data[i].ToString().IndexOf(_columnSeperator));
                            data[i] = data[i].ToString().Substring(data[i].ToString().IndexOf(_columnSeperator) + _columnSeperator.Length);
                        }
                    }
                    else
                    {
                        aColumn = data[i].ToString();
                        data[i] = "";
                    }

                    System.Globalization.CultureInfo usCulture = new System.Globalization.CultureInfo("en-US");
                    for (int j = 0; j < columnsIndex.Count; j++)
                    {
                        string[] indexes = (string[])columnsIndex[j];
                        string[] typeindexes = (string[])types[j];
                        for (int k = 0; k < indexes.Length; k++)
                        {
                            if (index.ToString() == indexes[k])
                            {
                                if (aColumn.Trim() == "")
                                    aColumn = "missing value";
                                try
                                {
                                    if (_targetType[Int32.Parse(typeindexes[k])].ToString().ToLower() == "double")
                                    {
                                        double tmp = Double.Parse(aColumn, System.Globalization.NumberStyles.Any, _numFormat);
                                        aColumn = tmp.ToString(usCulture);
                                    }
                                    else if (_targetType[Int32.Parse(typeindexes[k])].ToString().ToLower() == "datetime")
                                    {
                                        DateTime tmp = DateTime.Parse(aColumn, _dateTimeFormat);
                                        aColumn = tmp.ToString("yyyy-M-d H:m:s");
                                    }
                                }
                                catch (Exception exc)
                                {
                                    DBConnection.MyDebugClass.LogThis("Parsing value " + aColumn + " to " + _targetType[Int32.Parse(typeindexes[k])].ToString(), exc);
                                }
                                ((string[])queryData[j])[k] = aColumn;
                                k = indexes.Length;
                            }
                        }
                    }

                    index++;
                } while (data[i].ToString() != "");

                for (int j = 0; j < columnsIndex.Count; j++)
                {
                    tmpCmd = cmds[j];
                    string[] tmpData = (string[])queryData[j];
                    for (int k = 0; k < tmpData.Length; k++)
                    {
                        tmpCmd += "'" + tmpData[k] + "',";
                        tmpData[k] = "";
                    }
                    tmpCmd = tmpCmd.Remove(tmpCmd.Length - 1);
                    tmpCmd += ")";

                    if (scripts.Contains(tmpCmd) == false)
                        scripts.Add(tmpCmd);
                }
            }

            return scripts;
        }
        #endregion;
    }
}
